using System;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Windows.Forms;
using System.IO;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections;
using System.Reflection;
using System.Security.Permissions;
using System.ComponentModel;
using System.Drawing.Imaging;
using System.Runtime.InteropServices;
using System.Data;
using System.Data.Odbc;
using NumericalRecipes;
using BmgNet;
using StaticUtility;

namespace ObjectLib
{
	public class ExcelWrapper
	{
		public static string[] ColumnNames;
		public static readonly int MaxCols = 256;
		static ExcelWrapper()
		{
			//	Fill the "ColumnNames" variable.
			ColumnNames = new string[MaxCols];
			double mod, di;
			for(int i=0; i<MaxCols; i++)
			{
				string cn = "";
				di = (double)i+1.0;
				
				while(di >= 1.0)
				{
					//	0.1 in the line below accounts for tiny errors in the floating point calculation.
					mod = Math.Floor(26.0* ((di/26.0) - Math.Floor(di/26.0)) + 0.1);
					if(mod==0.0) mod = 26.0;
					
					cn = Convert.ToChar((int)mod + Convert.ToInt32('A') -1) + cn;
					
					di -= mod;
					di /= 26.0;
					//	The line below accounts for error in floating point calculation.
					di = Math.Floor(di + 0.1);

				}
				ColumnNames[i] = cn;
			}
		}
		public ExcelWrapper() {}
		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">string[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public static void SetRange(string[][] CellContents, int RowOffset, int ColOffset, Excel.Worksheet ws)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(CellContents[i][j], i+RowOffset, j+ColOffset, ws);
		}

		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">double[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public static void SetRange(double[][] CellContents, int RowOffset, int ColOffset, Excel.Worksheet ws)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(Convert.ToString(CellContents[i][j]), i+RowOffset, j+ColOffset, ws);
		}
		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">double[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public static void SetRange(float[][] CellContents, int RowOffset, int ColOffset, Excel.Worksheet ws)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(Convert.ToString(CellContents[i][j]), i+RowOffset, j+ColOffset, ws);
		}
		/// <summary>
		/// Generates a string representation of a cell with given row and column offsets (zero-based).
		/// </summary>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		/// <returns>string</returns>
		public static string CellAddress(int RowOffset, int ColOffset)
		{
			return ColumnNames[ColOffset] + Convert.ToString(RowOffset+1);
		}

		/// <summary>
		/// Sets the contents of a single cell.
		/// </summary>
		/// <param name="CellContents">string</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public static void SetCell(string CellContents, int RowOffset, int ColOffset, Excel.Worksheet ws)
		{
			string cell = CellAddress(RowOffset, ColOffset);
			((Excel.Range)ws.get_Range(cell,cell)).Value2 = CellContents;
		}
		public static object GetCell(int zbRow, int zbCol, Excel.Worksheet ws)
		{
			return ((Excel.Range)ws.Cells[zbRow+1,zbCol+1]).Value2;
		}

		public static void CopyContents(Excel.Worksheet copyFrom, Excel.Worksheet copyTo)
		{
			Excel.Range rngTemplate = copyFrom.UsedRange;
			Excel.Range endRngTemplate = rngTemplate.get_End(Excel.XlDirection.xlToRight).get_End(Excel.XlDirection.xlDown);
			int[][] range = RangeVals(rngTemplate.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, Type.Missing, Type.Missing));
			for(int r=range[0][0]; r<=range[1][0]; r++)
				for(int c=range[0][1]; c<=range[1][1]; c++)
					copyTo.Cells[r,c] = copyFrom.Cells[r,c];
		}
		/// <summary>
		/// Takes a string range reference in the style of Excel.XlReferenceStyle.xlR1C1 and converts it to 1-based integers.
		/// </summary>
		/// <param name="xlrc">string</param>
		/// <returns>int[0]  1-based index of the cell at the beginning of the range [row, col].
		/// int[1]  1-based index of the cell at the beginning of the range [row, col].</returns>
		public static int[][] RangeVals(string xlrc)
		{
			int[][] output = new int[2][];
			output[0] = new int[2];
			output[1] = new int[2];

			//	Get first row.
			int i=1;
			int j=i+1;
			while(xlrc[j]!='C')
				j++;
			output[0][0] = Convert.ToInt32(xlrc.Substring(i,j-i));

			//	Get first col.
			i=j+1;
			j=i+1;
			while(xlrc[j]!=':')
				j++;
			output[0][1] = Convert.ToInt32(xlrc.Substring(i,j-i));

			//	Get last row.
			i=j+2;
			j=i+1;
			while(xlrc[j]!='C')
				j++;
			output[1][0] = Convert.ToInt32(xlrc.Substring(i,j-i));

			//	Get last col.
			i=j+1;
			j=xlrc.Length;
			output[1][1] = Convert.ToInt32(xlrc.Substring(i,j-i));

			return output;
		}
	}
	/// <summary>
	/// Summary description for ExcelOutput.
	/// </summary>
	public class ExcelOutput
	{
		/// <summary>
		/// The maximum number of columns in an excel spreadsheet (static readonly).
		/// </summary>
		public static readonly int MAX_COLS = 256;
		/// <summary>
		/// The column name for each of MAX_COLS columns.  
		/// For example, ColumnNames[0] = "A" and ColumnNames[27] == "AB".  This is initialized
		/// by the empty constructor.
		/// </summary>
		public string[] ColumnNames;
		/// <summary>
		/// The excel application.
		/// </summary>
		public Excel.Application App;
		/// <summary>
		/// The excel workbook that the application is running.
		/// </summary>
		public Excel.Workbook Wb;
		/// <summary>
		/// The main output sheet that this object is currently working with.
		/// </summary>
		public Excel.Worksheet Ws;
		/// <summary>
		/// Opens a new excel worksheet for output.
		/// </summary>
		public ExcelOutput()
		{
			ConstructEmpty();
			App.Visible = true;
		}

		/// <summary>
		/// Generates an ExcelOutput object with given contents (starting at cell A1).
		/// </summary>
		/// <param name="CellContents">string[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public ExcelOutput(string[][] CellContents, int RowOffset, int ColOffset)
		{
			ConstructEmpty();
			SetRange(CellContents, RowOffset, ColOffset);
			App.Visible = true;
		}
		public ExcelOutput(double[][] CellContents, int RowOffset, int ColOffset)
		{
			ConstructEmpty();
			SetRange(CellContents, RowOffset, ColOffset);
			App.Visible = true;
		}
		public ExcelOutput(float[][] CellContents, int RowOffset, int ColOffset)
		{
			ConstructEmpty();
			SetRange(CellContents, RowOffset, ColOffset);
			App.Visible = true;
		}

		/// <summary>
		/// Constructs an empty object.  Includes a call to OpenWorkbook().  App, Wb, and Ws are initialized.
		/// ColumnNames is initialized.
		/// </summary>
		public void ConstructEmpty()
		{
			//	Opens a new workbook for output.
			OpenNewWorkbook();

			//	Selects the first worksheet to be the current output space.
			Ws = (Excel.Worksheet)Wb.Worksheets.get_Item("Sheet1");

			//	Fill the "ColumnNames" variable.
			ColumnNames = new string[MAX_COLS];
			double mod, di;
			for(int i=0; i<MAX_COLS; i++)
			{
				string cn = "";
				di = (double)i+1.0;
				
				while(di >= 1.0)
				{
					//	0.1 in the line below accounts for tiny errors in the floating point calculation.
					mod = Math.Floor(26.0* ((di/26.0) - Math.Floor(di/26.0)) + 0.1);
					if(mod==0.0) mod = 26.0;
					
					cn = Convert.ToChar((int)mod + Convert.ToInt32('A') -1) + cn;
					
					di -= mod;
					di /= 26.0;
					//	The line below accounts for error in floating point calculation.
					di = Math.Floor(di + 0.1);

				}
				ColumnNames[i] = cn;
			}
		}

		/// <summary>
		/// Generates a string representation of a cell with given row and column offsets (zero-based).
		/// </summary>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		/// <returns>string</returns>
		public string CellAddress(int RowOffset, int ColOffset)
		{
			return ColumnNames[ColOffset] + Convert.ToString(RowOffset+1);
		}

		/// <summary>
		/// Sets the contents of a single cell.
		/// </summary>
		/// <param name="CellContents">string</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public void SetCell(string CellContents, int RowOffset, int ColOffset)
		{
			string cell = CellAddress(RowOffset, ColOffset);
			((Excel.Range)Ws.get_Range(cell,cell)).Value2 = CellContents;
		}

		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">string[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public void SetRange(string[][] CellContents, int RowOffset, int ColOffset)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(CellContents[i][j], i+RowOffset, j+ColOffset);
		}

		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">double[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public void SetRange(double[][] CellContents, int RowOffset, int ColOffset)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(Convert.ToString(CellContents[i][j]), i+RowOffset, j+ColOffset);
		}
		/// <summary>
		/// Sets a range of cells.
		/// </summary>
		/// <param name="CellContents">float[][]</param>
		/// <param name="RowOffset">int zero-based</param>
		/// <param name="ColOffset">int zero-based</param>
		public void SetRange(float[][] CellContents, int RowOffset, int ColOffset)
		{
			for(int i=0; i<CellContents.Length; i++)
				for(int j=0; j<CellContents[i].Length; j++)
					SetCell(Convert.ToString(CellContents[i][j]), i+RowOffset, j+ColOffset);
		}

		/// <summary>
		/// Opens a new workbook for output.
		/// </summary>
		public void OpenNewWorkbook()
		{
			//	Begins an excel application.
			App = new Excel.ApplicationClass();

			// Adds a new workbook
			Wb = App.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
		}
	}
}